﻿Imports System
Imports System.Data.OleDb
Imports System.Data
Imports System.Collections


Public Class DataProvider
    Public Shared ReadOnly Property ConnectionString() As String
        Get
            Return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=QuanLyXE.mdb;Persist Security Info=True"
        End Get
    End Property

    Private conn As OleDbConnection

    Public Function Connect() As OleDbConnection

        If conn Is Nothing Then
            conn = New OleDbConnection(ConnectionString)
        End If

        If (conn.State <> ConnectionState.Open) Then
            Try
                conn.Open()
            Catch ex As Exception
                Throw New Exception(ex.Message)
            End Try
        End If

        Return conn
    End Function

    Public Sub DisConnect()
        If Not (conn Is Nothing) AndAlso conn.State = ConnectionState.Open Then
            conn.Close()
        End If
    End Sub

    Public Function ThucThiCauTruyVan(ByVal sql As String) As DataTable
        Try
            Return ThucThiCauTruyVan(sql, Nothing)
        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try
    End Function
    Public Function ThucThiCauTruyVan(ByVal sql As String, ByVal dsParameter As List(Of OleDbParameter)) As DataTable
        Try
            Connect()

            Dim command As OleDbCommand
            Dim dt As DataTable = New DataTable
            command = New OleDbCommand(sql, conn)

            If dsParameter IsNot Nothing Then
                command.Parameters.AddRange(dsParameter.ToArray())
            End If

            Dim adapter As New OleDbDataAdapter(command)
            adapter.Fill(dt)

            Return dt

        Catch ex As Exception
            Throw New Exception(ex.Message)
        Finally '??? dung lam gi?
            DisConnect()
        End Try
    End Function
    Public Function ThucThiTruyVan(ByVal sql As String) As Integer
        Try
            Connect()
            Dim command As OleDbCommand
            command = New OleDbCommand(sql, conn)

            Return command.ExecuteNonQuery()

        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try
    End Function
    Public Function ThucThiTruyVanThuong(ByVal sql As String, ByVal dsParameter As List(Of OleDbParameter)) As Integer
        Try
            Connect()

            Dim command As OleDbCommand
            command = New OleDbCommand(sql, conn)

            If dsParameter IsNot Nothing Then
                command.Parameters.AddRange(dsParameter.ToArray())
            End If

            Return command.ExecuteNonQuery()
        Catch ex As Exception
            'Throw New Exception(ex.Message)
        Finally
            DisConnect()
        End Try

    End Function


    'Hàm này dùng để đưa dữ liệu từ datatable vào bảng có tên 'tenbang'
    Public Function CapNhatXuongCSDL(ByVal tenBang As String, ByVal dt As DataTable) As Integer
        Dim kq As Integer = 0
        Try
            Connect()
            Dim command As New OleDbCommand("Select * From " & tenBang, conn)

            Dim adapter As New OleDbDataAdapter()
            adapter.SelectCommand = command

            Dim cmb As New OleDbCommandBuilder(adapter)
            kq = adapter.Update(dt)

            Return kq
        Catch ex As Exception
            Throw New Exception(ex.Message)
        Finally
            DisConnect()
        End Try
    End Function

End Class